﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using Negocio;

namespace Persistencia
{
    class BrokerEvaluation : Broker
    {
        public BrokerEvaluation()
        {
            this.conn = ConnectionDB.getInstance();
        }

        public override void add(Object o)
        {
            Evaluation evaluation = (Evaluation)o;
            this.conn.connect();
            string sql = "insert into Evaluation(Oid,name,description) values('" + evaluation.OID + "', '" + evaluation.Name + "', '" + evaluation.Description + "' )";
            string sql1 = "update OID set OId=" + evaluation.OID;
            this.conn.execute(sql);
            this.conn.execute(sql1);
            ObservableCollection<Test> list = evaluation.Tests;
            IEnumerator<Test> t = list.GetEnumerator();
            while (t.MoveNext())
            {
                if (t.Current.OID == 0) 
                {
                    t.Current.added();
                }                
                string sql2 = "insert into EvaluationTest (oidEvaluation,oidTest) values(" + evaluation.OID + "," + ((Test)t.Current).OID + ")";
                this.conn.execute(sql2);
            }
            this.conn.disconnect();
        }

        public override void update(Object o)
        {
            Evaluation evaluation = (Evaluation)o;
            string sql = "update Evaluation set name='" + evaluation.Name + "', description = '" + evaluation.Description + "' where oid = " + evaluation.OID;
            this.conn.connect();
            this.conn.execute(sql);
            string sql1 = "delete * from EvaluationTest where oidEvaluation = " + evaluation.OID;
            this.conn.execute(sql1);
            string sql2 = "delete * from TestMeasure where oidTest not in(select et.oidTest from Evaluation e,EvaluationTest et where e.Oid=et.oidEvaluation and e.oid=" + evaluation.OID + ")";
            //this.conn.execute(sql2);
            //sentencia para borrar las medidas que no son usadas por la evaluacion que recibo por parametros de esta manera no dejamos basura en la base osea medidas huerfanas
            string sql3 = "delete * from ReferenceMeasure where oid not in(select tm.oidReference from Evaluation e,EvaluationTest et,TestMeasure tm where e.Oid=et.oidEvaluation and tm.oidTest=et.oidTest and e.oid=" + evaluation.OID + ")";
            //this.conn.execute(sql3);
            //sentencia para borrar las medidas que no son usadas por la evaluacion que recibo por parametros de esta manera no dejamos basura en la base osea test huerfanos
            string sql4 = "delete * from Test where oidTest not in(select et.oidTest from Evaluation e,EvaluationTest et where e.Oid=et.oidEvaluation and e.oid=" + evaluation.OID + ")";
            //this.conn.execute(sql4);

            ObservableCollection<Test> list = evaluation.Tests;
            IEnumerator<Test> t = list.GetEnumerator();
            while (t.MoveNext())
            {
                if (t.Current.OID == 0)
                {
                    t.Current.added();                    
                }
                string sql5 = "insert into EvaluationTest (oidEvaluation,oidTest) values(" + evaluation.OID + "," + ((Test)t.Current).OID + ")";
                this.conn.execute(sql5);
            }

            this.conn.disconnect();
        }

        public override void delete(Object o)
        {
            Evaluation evaluation = (Evaluation)o;
            this.conn.connect();
            string sql = "delete * from EvaluationTest where oidEvaluation = " + evaluation.OID;
            this.conn.execute(sql);
            string sql1 = "delete * from Evaluation where oid = " + evaluation.OID;
            this.conn.execute(sql1);
            ObservableCollection<Test> list = evaluation.Tests;
            IEnumerator<Test> t = list.GetEnumerator();
            while (t.MoveNext())
            {
                t.Current.removed();
            }
            this.conn.disconnect();
        }


        public override void bringData(Object o)
        {
            ObservableCollection<Object> list = new ObservableCollection<Object>();
            Evaluation evaluation = (Evaluation)o;
            this.conn.connect();
            string sql = "Select * from Evaluation where oid =" + evaluation.OID;
            this.conn.bringData(sql);
            while (this.conn.nextRecord())
            {
                evaluation.Name = this.conn.getDataString("name").ToString();
                evaluation.OID = int.Parse(this.conn.getDataString("Oid").ToString());
                evaluation.Description = this.conn.getDataString("Description").ToString();
            }
                string sql1 = "Select * from Test where oidEvaluation=" + evaluation.OID;
                this.conn.bringList(sql1);
                while (this.conn.nextRecord())
                {
                    Test test = new Test();
                    test.Name = this.conn.getDataString("name").ToString();
                    test.MeasureUnit = this.conn.getDataString("measureUnit").ToString();
                    test.Sequence = this.conn.getDataString("Sequence").ToString();
                    test.OID = int.Parse(this.conn.getDataString("oidTest").ToString());
                    evaluation.addTest(test);
                    list.Add(test);

                }
                    IEnumerator<Object> p = list.GetEnumerator();
                        while (p.MoveNext())
                            {
                    string sql2 = "Select r.* from TestMeasure tm,ReferenceMeasure r where tm.oidReference=r.oid and tm.oidTest=" + ((Test)p.Current).OID;
                    this.conn.bringList(sql2);
                    while (this.conn.nextRecord())
                    {
                        ReferenceMeasure measure = new ReferenceMeasure();
                        measure.Name = this.conn.getDataString("name").ToString();
                        measure.Value = float.Parse(this.conn.getDataString("value").ToString());
                        measure.OID = int.Parse(this.conn.getDataString("oid").ToString());
                        ((Test)p.Current).addReferenceMeasure(measure);
                    }

                            }
               this.conn.disconnect();
        }

        public override ObservableCollection<Object> bringList(Object o)
        {
            ObservableCollection<Object> list = new ObservableCollection<Object>();
            ObservableCollection<Object> list1 = new ObservableCollection<Object>();
            string sql = "Select * from Evaluation order by name";
            this.conn.connect();
            this.conn.bringData(sql);
            while (this.conn.nextRecord())
            {
                Evaluation evaluation = new Evaluation();
                evaluation.Name = this.conn.getDataString("name").ToString();
                evaluation.OID = int.Parse(this.conn.getDataString("Oid").ToString());
                evaluation.Description = this.conn.getDataString("Description").ToString();
                list.Add(evaluation);
            }
            IEnumerator<Object> p = list.GetEnumerator();
            while (p.MoveNext())
            {
                string sql1 = "Select t.* from Test t,EvaluationTest et where et.oidTest=t.oidTest and et.oidEvaluation=" + ((Evaluation)p.Current).OID;
                this.conn.bringList(sql1);
                while (this.conn.nextRecord())
                {
                    Test test = new Test();
                    test.Name = this.conn.getDataString("name").ToString();
                    test.MeasureUnit = this.conn.getDataString("measureUnit").ToString();
                    test.Sequence = this.conn.getDataString("Sequence").ToString();
                    test.OID = int.Parse(this.conn.getDataString("oidTest").ToString());
                    ((Evaluation)p.Current).addTest(test);
                    list1.Add(test);
                }
            }
            /*
            IEnumerator<Object> l = list1.GetEnumerator();
            while (l.MoveNext())
            {
                string sql2 = "Select r.* from ReferenceMeasure r,TestMeasure tm where tm.oidReference=r.oid and tm.oidTest=" + ((Test)l.Current).OID;
                this.conn.bringList(sql2);
                while (this.conn.nextRecord())
                {
                    ReferenceMeasure measure = new ReferenceMeasure();
                    measure.Name = this.conn.getDataString("name").ToString();
                    measure.Value = float.Parse(this.conn.getDataString("value_").ToString());
                    measure.OID = int.Parse(this.conn.getDataString("oid").ToString());
                    ((Test)l.Current).addReferenceMeasure(measure);
                }

            }*/             
            this.conn.disconnect();
            return list;
        }


    }
}
